import * as duckdb from '@duckdb/duckdb-wasm';
import duckdb_wasm from '@duckdb/duckdb-wasm/dist/duckdb-mvp.wasm?url';
import mvp_worker from '@duckdb/duckdb-wasm/dist/duckdb-browser-mvp.worker.js?url';
import duckdb_wasm_eh from '@duckdb/duckdb-wasm/dist/duckdb-eh.wasm?url';
import eh_worker from '@duckdb/duckdb-wasm/dist/duckdb-browser-eh.worker.js?url';

// duckdb 初始化
const MANUAL_BUNDLES = {
    mvp: {
        mainModule: duckdb_wasm,
        mainWorker: mvp_worker,
    },
    eh: {
        mainModule: duckdb_wasm_eh,
        mainWorker: eh_worker,
    },
};
// Select a bundle based on browser checks
const bundle = await duckdb.selectBundle(MANUAL_BUNDLES);
// Instantiate the asynchronus version of DuckDB-wasm
const worker = new Worker(bundle.mainWorker);
const logger = new duckdb.ConsoleLogger();
const db = new duckdb.AsyncDuckDB(logger, worker);
await db.instantiate(bundle.mainModule, bundle.pthreadWorker);


// 导入parquet文件

// console.log("Init DB Dconnection data ...")
// await initDB();
// console.log("DB: ", db)

// await db.reset()
await db.registerFileURL('fund-desc.parquet', 'http://localhost:9002/fund-desc.parquet', duckdb.DuckDBDataProtocol.HTTP, false);
const resFund = await fetch('http://localhost:9002/fund-desc.parquet');
await db.registerFileBuffer('fund-desc.parquet', new Uint8Array(await resFund.arrayBuffer()));

await db.registerFileURL('fund-nav.parquet', 'http://localhost:9002/fund-nav.parquet', duckdb.DuckDBDataProtocol.HTTP, false);
const resNav = await fetch('http://localhost:9002/fund-nav.parquet');
await db.registerFileBuffer('fund-nav.parquet', new Uint8Array(await resNav.arrayBuffer()));

const coon = await db.connect()
await coon.query("CREATE TABLE fund_desc AS SELECT * FROM 'fund-desc.parquet';")
await coon.query("CREATE TABLE fund_nav AS SELECT * FROM 'fund-nav.parquet';")

// console.log('end')

// 从fund-desc中读取数据
const getFundData = async (sql) => {
    console.log('getFundData')
    const conn = await db.connect()

    // arrow table to json
    const arrowResult = await conn.query(sql)
    const ans = arrowResult.toArray().map((row) => row.toJSON());

    // console.log(ans)
    return ans
}

// 从fund-nav中读取数据
const getNavData = async (sql) => {
    console.log('getNavData')
    const conn = await db.connect()
    
    // arrow table to json
    const arrowResult = await conn.query(sql)
    const ans = arrowResult.toArray().map((row) => row.toJSON());

    // console.log(ans)
    return ans
}

// 计算基金日收益率
const chg = async () => {
    console.log('chg')
    let sql1 = "ALTER TABLE fund_nav ADD COLUMN chg DOUBLE; "
    let sql2 = "WITH PrevNav AS ( SELECT ticker, dt, nav, LAG(nav, 1) OVER (PARTITION BY ticker ORDER BY dt) AS prev_nav FROM fund_nav ) " + 
    "UPDATE fund_nav SET chg = (PrevNav.nav - PrevNav.prev_nav) - 1 FROM PrevNav WHERE fund_nav.ticker = PrevNav.ticker AND fund_nav.dt = PrevNav.dt;"
    const conn = await db.connect()
    // const ans1 = await conn.query("select * from fund_nav")
    // console.log("chg ans1:", ans1)

    await conn.query(sql1)
    await conn.query(sql2)
    // const ans = await conn.query("select * from fund_nav")
    // console.log("chg ans:", ans)
    // return ans

}
chg()


export { getFundData, getNavData  }; // so we can import this elsewhere